package datamodule;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import util.Criptografia;
import util.FString;
import conexao.Connect;

public class UsuarioDAO {


	private DBuserVO userVO;

	public UsuarioDAO( DBuserVO userVO){
		this.userVO = userVO;
	}

	/**
	 * @category Persistencia
	 * @since  23/03/2013
	 * @author Fabricio
	 * @param VOusu
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public int inserir(UsuarioVO VOusu) throws ClassNotFoundException, SQLException {
		Connection con = Connect.getConnection(userVO);
		PreparedStatement pstmtInsert = con.prepareStatement(
				"INSERT INTO usuario(usu_codusu, usu_operad, usu_senhau, " +
				" usu_nomusu, usu_tempex, usu_horini, usu_horfin, usu_remoto, igr_Cod_Igreja, " +
				" sql_deleted, usu_ativoc) "			  
				+" VALUES(?,?,?,?,?,?,?,?,?,?,?) ");
		pstmtInsert.setString(1, VOusu.getUsu_codusu());			
		pstmtInsert.setString(2, VOusu.getUsu_operad());
		pstmtInsert.setString(3, VOusu.getUsu_senhau_Crip());
		pstmtInsert.setString(4, VOusu.getUsu_nomusu());
		pstmtInsert.setString(5, VOusu.getUsu_tempex());
		pstmtInsert.setString(6, VOusu.getUsu_horini());
		pstmtInsert.setString(7, VOusu.getUsu_horfin());
		pstmtInsert.setString(8, VOusu.getUsu_remoto());
		pstmtInsert.setString(9, VOusu.getIgr_Cod_Igreja());
		pstmtInsert.setString(10,"F");
		pstmtInsert.setString(11,VOusu.getUsu_ativoc());
		int reg = pstmtInsert.executeUpdate();	
		con.close();
		return reg;
	}	


	/**
	 * @category Persistencia
	 * @since  23/03/2013
	 * @author Fabricio
	 * @param VOusu
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public int alterar(UsuarioVO VOusu) throws ClassNotFoundException, SQLException {
		Connection con = Connect.getConnection(userVO);
		String virgula = "";
		StringBuffer sql = new StringBuffer(" UPDATE usuario SET ");
		if (!VOusu.getUsu_operad().equals(FString.ignorar)) {
			sql.append(virgula+" usu_operad = '"+VOusu.getUsu_operad()+"' ");
			virgula = ",";
		}
		if (!VOusu.getUsu_nomusu().equals(FString.ignorar)) {
			sql.append(virgula+" usu_nomusu = '"+VOusu.getUsu_nomusu()+"' ");
			virgula = ",";
		}
		if (!VOusu.getUsu_senhau().equals(FString.ignorar)) {
			sql.append(virgula+" usu_senhau = '"+VOusu.getUsu_senhau_Crip()+"' ");
			virgula = ",";
		}
		if (!VOusu.getUsu_tempex().equals(FString.ignorar)) {
			sql.append(virgula+" usu_tempex = '"+VOusu.getUsu_tempex()+"' ");
			virgula = ",";
		}
		if (!VOusu.getUsu_horini().equals(FString.ignorar)) {
			sql.append(virgula+" usu_horini = '"+VOusu.getUsu_horini()+"' ");
			virgula = ",";
		}
		if (!VOusu.getUsu_horfin().equals(FString.ignorar)) {
			sql.append(virgula+" usu_horfin = '"+VOusu.getUsu_horfin()+"' ");
			virgula = ",";
		}
		
		if (!VOusu.getIgr_Cod_Igreja().equals(FString.ignorar)) {
			sql.append(virgula+" igr_Cod_Igreja = '"+VOusu.getIgr_Cod_Igreja()+"' ");
			virgula = ",";
		}
		
		if (!VOusu.getUsu_remoto().equals(FString.ignorar)) {
			sql.append(virgula+" usu_remoto = '"+VOusu.getUsu_remoto()+"' ");
			virgula = ",";
		}	
		if (!VOusu.getUsu_ativoc().equals(FString.ignorar)) {
			sql.append(virgula+" usu_ativoc = '"+VOusu.getUsu_ativoc()+"' ");
			virgula = ",";
		}
		
		sql.append(" WHERE usu_codusu = '"+VOusu.getUsu_codusu()+"' AND sql_deleted <> 'T' ");
		PreparedStatement pstmt = con.prepareStatement(sql.substring(0, sql.length()));
		int registrosAfetados = pstmt.executeUpdate();
		con.close();
		return registrosAfetados;
	}

	/**
	 * @category Persistencia
	 * @since  23/03/2013
	 * @author Fabricio
	 * @param VOusu
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public int deletar(UsuarioVO VOusu) throws ClassNotFoundException, SQLException {		
		Connection con = Connect.getConnection(userVO);
		PreparedStatement pstmt = con.prepareStatement(
		"DELETE FROM usuario WHERE usu_codusu = ?");
		pstmt.setString(1, VOusu.getUsu_codusu());
		int registrosAfetados = pstmt.executeUpdate();
		con.close();
		return registrosAfetados;
	}

	/**
	 * Pesquisa pelo usu_codusu, usu_operad, usu_nivela
	 * @category Persistencia
	 * @since  23/03/2013
	 * @author Fabricio
	 * @param VOusu
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public UsuarioVO pesquisar(UsuarioVO VOusu) throws ClassNotFoundException, SQLException {				
		Connection con = Connect.getConnection(userVO);
		StringBuffer sql = new StringBuffer("SELECT u.* FROM usuario u " +
				"WHERE u.sql_deleted <> 'T' ");

		if (!VOusu.getUsu_codusu().equals(FString.ignorar)) {
			sql.append(" AND UPPER(usu_codusu) = '"+VOusu.getUsu_codusu()+"' ");			
		}		
		if (!VOusu.getUsu_operad().equals(FString.ignorar)) {
			sql.append(" AND UPPER(usu_operad) = '"+VOusu.getUsu_operad()+"' ");			
		}
		//System.out.println(sql);
		PreparedStatement pstmt = con.prepareStatement(sql.substring(0,sql.length()));		
		ResultSet rs = pstmt.executeQuery();
		if (rs.next()) {
			VOusu = new UsuarioVO();
			VOusu.setUsu_codusu    (rs.getString("usu_codusu"));
			VOusu.setUsu_operad    (rs.getString("usu_operad"));
			VOusu.setUsu_senhau    (rs.getString("usu_senhau"));
			VOusu.setUsu_tempex    (rs.getString("usu_tempex"));
			VOusu.setUsu_nomusu    (rs.getString("usu_nomusu"));
			VOusu.setUsu_horini    (rs.getString("usu_horini"));
			VOusu.setUsu_horfin    (rs.getString("usu_horfin"));
            VOusu.setIgr_Cod_Igreja(rs.getString("Igr_Cod_Igreja"));         
			VOusu.setUsu_remoto    (rs.getString("usu_remoto"));
			VOusu.setUsu_ativoc    (rs.getString("usu_ativoc"));
			VOusu.setSql_rowid     (rs.getInt   ("sql_rowid"));
			VOusu.setSql_deleted   (rs.getString("sql_deleted"));
		}else {
			VOusu = null;
		}
		pstmt.close();
		rs.close();
		con.close();
		return VOusu;
	}

	/**	 
	 * @category Persistencia
	 * @since  23/03/2013
	 * @author Fabricio
	 * @param VOusu
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public ArrayList<UsuarioVO> geral(UsuarioVO VOusu) throws SQLException, ClassNotFoundException {		
		ArrayList<UsuarioVO> lista = null;
		Connection con = Connect.getConnection(userVO);
		StringBuffer sql = new StringBuffer(
		"SELECT * FROM usuario WHERE sql_deleted <> 'T' ");
		if (!VOusu.getUsu_codusu().equals(FString.ignorar)) {
			sql.append(" AND UPPER(usu_codusu) LIKE UPPER('"+VOusu.getUsu_codusu()+"%"+"')");
		}
		if (!VOusu.getUsu_operad().equals(FString.ignorar)) {
			sql.append(" AND UPPER(usu_operad) LIKE UPPER('"+VOusu.getUsu_operad()+"%"+"')");
		}
		if (!VOusu.getUsu_nomusu().equals(FString.ignorar)) {
			sql.append(" AND UPPER(usu_nomusu) LIKE UPPER('"+VOusu.getUsu_nomusu()+"%"+"')");
		}
		
		if (!VOusu.getOrdem().equals(FString.ignorar)) {
			sql.append(" ORDER BY "+VOusu.getOrdem());
		}
		if (!VOusu.getLimit().equals(FString.ignorar)) {
			sql.append(" LIMIT "+VOusu.getLimit());
		}
		PreparedStatement pstmt = con.prepareStatement(sql.substring(0,sql.length()));		
		ResultSet rs = pstmt.executeQuery();
		if (rs.next()) {
			lista = new ArrayList<UsuarioVO>();
			do {
				UsuarioVO vo = new UsuarioVO();
				vo.setUsu_codusu    (rs.getString("usu_codusu"));
				vo.setUsu_operad    (rs.getString("usu_operad"));
				vo.setUsu_senhau    (rs.getString("usu_senhau"));
				vo.setUsu_tempex    (rs.getString("usu_tempex"));
				vo.setUsu_nomusu    (rs.getString("usu_nomusu"));
				vo.setUsu_horini    (rs.getString("usu_horini"));
				vo.setUsu_horfin    (rs.getString("usu_horfin"));
				vo.setIgr_Cod_Igreja(rs.getString("igr_Cod_Igreja"));
				vo.setUsu_remoto    (rs.getString("usu_remoto"));
				vo.setUsu_ativoc    (rs.getString("usu_ativoc"));
				vo.setSql_rowid     (rs.getInt   ("sql_rowid"));
				vo.setSql_deleted   (rs.getString("sql_deleted"));

				lista.add(vo);
			}
			while (rs.next());			
		}
		pstmt.close();
		rs.close();
		con.close();
		return lista;
	}		
	
	public int alterarSenha(String usu_codusu, String usu_senhau) throws ClassNotFoundException, SQLException {				
		int registrosAfetados = 0;
		Connection con = Connect.getConnection(userVO);
		PreparedStatement pstmtInsert = con.prepareStatement(
				"Update usuario "
				+"Set usu_senhau = ?, usu_logsit = '1'  "					
				+"WHERE usu_codusu = ? AND sql_deleted <> 'T' ");

		pstmtInsert.setString(1,Criptografia.criptografa(usu_senhau.trim().toUpperCase(), " !"));
		pstmtInsert.setString(2,usu_codusu.toUpperCase());				

		registrosAfetados = pstmtInsert.executeUpdate();
		con.close();
		return registrosAfetados;

	}
}